<?php

namespace App\Admin\Controllers\Sys;

use App\Admin\Extensions\CustomExporter;
use App\Admin\Extensions\ExcelExpoter;
use App\Admin\Extensions\Exporter\SchoolExporter;
use App\Admin\Extensions\Widgets\InfoBoxII;
use App\Models\School\Classes;
use App\Models\School\Teacher;
use App\Models\Sys\Child;
use App\Models\Sys\School;
use App\Models\User\User;
use Encore\Admin\Form;
use Encore\Admin\Grid;
use Encore\Admin\Facades\Admin;
use Encore\Admin\Layout\Content;
use App\Http\Controllers\Controller;
use Encore\Admin\Controllers\ModelForm;
use Encore\Admin\Layout\Row;
use Encore\Admin\Widgets\Tab;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class SchoolController extends Controller
{
    use ModelForm;

    public $header = '学校列表';

    /**
     * Index interface.
     *
     * @return Content
     */
    public function index()
    {
        return Admin::content(function (Content $content) {

            $content->header($this->header);
            $content->description('description');

            /*$tab = new Tab();
            $tab->add('学校', '');
            $tab->add('学校1', '', true);
            $content->body($tab);*/

            $content->body($this->grid());
        });
    }

    /**
     * Edit interface.
     *
     * @param $id
     * @return Content
     */
    public function edit($id)
    {
        return Admin::content(function (Content $content) use ($id) {

            $content->header($this->header);
            $content->description('description');

            $content->body($this->form()->edit($id));


        });
    }

    protected function grid()
    {
        return Admin::grid(School::class, function (Grid $grid) {

            $grid->model()->select(['id','school_name','telephone','province','city','district','address'])
            ->with('classes');

            $grid->id('序号')->sortable();
            $grid->school_name('学校名称');
            $grid->telephone('电话');
            $grid->province('省');
            $grid->city('市');
            $grid->district('区');
            $grid->address('地址');

            /*$grid->column('address', '地址')->display(function (){
                return $this->province . ' ' . $this->city . ' ' . $this->district . ' ' . $this->address;
            });*/

            //$grid->location('详细地址(坐标)');

            /*$grid->column('teacher_count', '老师数')->display(function (){
                return School::getTeacher($this->classes);
            });*/

            $grid->column('teacher_count', '老师数')->display(function (){
                return School::getTeacherCount($this->id);
            });

            $grid->column('parent_count', '家长数')->display(function (){
                return School::getParentCount($this->id);
            });

            $grid->actions(function(Grid\Displayers\Actions $actions){
                $actions->disableDelete();
                $actions->disableEdit();
            });

            $grid->disableCreateButton();

            $grid->filter(function (Grid\Filter $filter){
                $filter->disableIdFilter();
                $filter->like('school_name', '学校名称');
            });

            //导出
            $grid->exporter(new SchoolExporter($grid, '学校列表'));

        });
    }

    protected function grid1()
    {
        return Admin::grid(School::class, function (Grid $grid) {

            $grid->model()->from('jxt_sys_school as a')
                ->leftJoin('jxt_school_classes as b',function ($join){
                    $join->on('b.school_code', 'a.id')->where('b.status', 1);
                })
                ->leftJoin('jxt_school_student as c',function ($join){
                    $join->on('c.class_id', 'b.class_id')->where('c.status', 1);
                })
                ->leftJoin('jxt_school_child_student as d',function ($join){
                    $join->on('d.student_id', 'c.student_id')->where('d.status', 1);
                })
                ->leftJoin('jxt_sys_child as e',function ($join){
                    $join->on('e.child_id', 'd.child_id')->where('e.status', 1);
                })
                ->leftJoin('jxt_school_teacher as f',function ($join){
                    $join->on('f.class_id', 'b.class_id')->where('f.status', 1);
                })
                ->where('a.status', 1)
                ->selectRaw('a.id,a.school_name,a.telephone,a.province,a.city,a.district,a.address,a.location,COUNT(DISTINCT(e.create_by)) AS parent_count,COUNT(DISTINCT(f.teacher_id)) AS teacher_count')
                ->groupBy('a.id');

            $grid->id('序号')->sortable();
            $grid->school_name('学校名称');
            $grid->telephone('电话');
            $grid->column('地址')->display(function (){
                return $this->province . ' ' . $this->city . ' ' . $this->district . ' ' . $this->address;
            });
            //$grid->location('详细地址(坐标)');
            $grid->parent_count('家长数');
            $grid->teacher_count('老师数');

            //$grid->disableActions();

            $grid->actions(function(Grid\Displayers\Actions $actions){
                $actions->disableDelete();
                $actions->disableEdit();
            });

            $grid->disableCreateButton();

            $grid->filter(function (Grid\Filter $filter){
                $filter->disableIdFilter();
                $filter->like('school_name', '学校名称');
            });

            $head = ['序号', '学校名称', '电话', '省', '市', '区', '详细地址', '详细地址(坐标)', '老师数', '家长数'];
            $body = ['id', 'school_name', 'telephone', 'province', 'city', 'district', 'address', 'location', 'teacher_count', 'parent_count'];
            $grid->exporter(new CustomExporter($grid, '学校列表', $head, $body));

        });
    }

    public function show($id, Request $request)
    {
        return Admin::content(function (Content $content) use ($id, $request) {

            $content->header($this->header);
            $content->description('description');

            $school_count_data = School::from('jxt_sys_school as a')
                ->leftJoin('jxt_school_classes as b',function ($join){
                    $join->on('b.school_code', 'a.id')->where('b.status', 1);
                })
                ->leftJoin('jxt_school_student as c',function ($join){
                    $join->on('c.class_id', 'b.class_id')->where('c.status', 1);
                })
                ->leftJoin('jxt_school_child_student as d',function ($join){
                    $join->on('d.student_id', 'c.student_id')->where('d.status', 1);
                })
                ->leftJoin('jxt_sys_child as e',function ($join){
                    $join->on('e.child_id', 'd.child_id')->where('e.status', 1);
                })
                ->leftJoin('jxt_school_teacher as f',function ($join){
                    $join->on('f.class_id', 'b.class_id')->where('f.status', 1);
                })
                ->where('a.status', 1)
                ->where('a.id', $id)
                ->selectRaw('COUNT(DISTINCT(f.teacher_id)) AS teacher_count,COUNT(DISTINCT(e.create_by)) AS parent_count,COUNT(DISTINCT(c.student_id)) AS student_count')
                ->first();

            //老师 vip和安卓端购买校币
            $teacher_order_info = DB::table('jxt_school_classes as a')
                ->leftJoin('jxt_school_teacher AS b', 'b.class_id', 'a.class_id')
                ->leftJoin('jxt_order_info as c', function ($join) {
                    $join->on('c.user_id', 'b.user_id')->whereIn('c.type', [2,3])->where('c.pay_status', 2);
                })
                ->where('a.school_code', $id)
                ->sum('c.order_amount');
            //老师 ios端购买校币
            $teacher_sys_order = DB::table('jxt_school_classes as a')
                ->leftJoin('jxt_school_teacher as b', 'b.class_id', 'a.class_id')
                ->leftJoin('jxt_sys_orders as c', function ($join) {
                    $join->on('c.user_id', 'b.user_id')->where('c.status', 1);
                })
                ->leftJoin('jxt_sys_golds as d', 'd.gold_id', 'c.gold_id')
                ->where('a.school_code', $id)
                ->sum('d.price');
            $teacher_total_price = $teacher_order_info + $teacher_sys_order;

            //家长 vip和安卓端购买校币
            $parent_order_info = DB::table('jxt_school_classes as a')
                ->leftJoin('jxt_school_student as b', 'b.class_id', 'a.class_id')
                ->leftJoin('jxt_school_child_student as c', 'c.student_id', 'b.student_id')
                ->leftJoin('jxt_sys_child as d', 'd.child_id', 'c.child_id')
                ->leftJoin('jxt_order_info as e', function ($join) {
                    $join->on('e.user_id', 'd.create_by')->where('e.type', 1)->where('e.pay_status', 2);
                })
                ->where('a.school_code', $id)
                ->sum('e.order_amount');

            //家长 ios端购买校币
            $parent_sys_order = DB::table('jxt_school_classes as a')
                ->leftJoin('jxt_school_student as b', 'b.class_id', 'a.class_id')
                ->leftJoin('jxt_school_child_student as c', 'c.student_id', 'b.student_id')
                ->leftJoin('jxt_sys_child as d', 'd.child_id', 'c.child_id')
                ->leftJoin('jxt_sys_orders as e', function ($join) {
                    $join->on('e.user_id', 'd.create_by')->where('e.status', 1);
                })
                ->leftJoin('jxt_sys_golds as f', 'f.gold_id', 'e.gold_id')
                ->where('a.school_code', $id)
                ->sum('f.price');
            $parent_total_price = $parent_order_info + $parent_sys_order;

            $tab = new Tab();

            $limit = $request->get('per_page', 20);

            $query = parse_url($_SERVER['REQUEST_URI']);
            $params = [];
            $current_page = 'p0';

            if (isset($query['query'])) {
                $queryParts = explode('&', $query['query']);
                foreach ($queryParts as $param) {
                    $item = explode('=', $param);
                    $params[$item[0]] = $item[1];
                }
                end($params);

                $current_page = key($params);
            }

            $tab->add('老师数', $this->gridTeacher($id, $limit)->render(), 'p0' == $current_page ? true : false);
            $tab->add('家长数', $this->gridParent($id, $limit)->render(), 'p1' == $current_page ? true : false);
            $tab->add('老师会员数', $this->gridTeacherMember($id, $limit)->render(), 'p2' == $current_page ? true : false);
            $tab->add('家长订阅数', $this->gridParentSubscribe($id, $limit)->render(), 'p3' == $current_page ? true : false);

            $content->row(function (Row $row) use ($school_count_data, $teacher_total_price, $parent_total_price) {

                $teacher_count = $school_count_data->teacher_count ? $school_count_data->teacher_count : 0;
                $parent_count = $school_count_data->parent_count ? $school_count_data->parent_count : 0;
                $student_count = $school_count_data->student_count ? $school_count_data->student_count : 0;
                $school_all_count = $teacher_count + $parent_count + $student_count;

                $row->column(3, new InfoBoxII('老师数', 'file-text', 'aqua', $teacher_count, '充值金额: ' . $teacher_total_price));
                $row->column(3, new InfoBoxII('家长数', 'jpy', 'green', $parent_count, '充值金额: ' . $parent_total_price));
                $row->column(3, new InfoBoxII('学生数', 'pie-chart', 'yellow', $student_count, '&nbsp;'));
                $row->column(3, new InfoBoxII('学校总人数', 'bar-chart', 'red', $school_all_count, '充值金额: ' . ($teacher_total_price + $parent_total_price)));

            })
            ->row($tab);

        });
    }

    public function gridTeacher($id, $limit)
    {
        //老师数
        $grid = new Grid(new Teacher);
        $grid->model()->from('jxt_school_teacher AS a')
            ->leftJoin('jxt_school_classes as b', function ($join) use ($id) {
                $join->on('b.class_id', 'a.class_id')->where('b.school_code', $id);
            })
            ->leftJoin('jxt_user_account as c', 'c.user_id', 'a.user_id')
            ->leftJoin('jxt_xp_user as d', 'd.id', 'a.user_id')
            ->where('b.school_code', $id)
            ->where('a.status', 1)
            ->where('d.status', 1)
            ->select(['d.id', 'd.username', 'd.telephone', 'c.expe', 'd.created_at']);

        $grid->username('姓名');
        $grid->telephone('手机号');
        $grid->expe('积分');
        $grid->created_at('创建时间');
        $grid->disableExport();
        $grid->disableCreateButton();
        $grid->disableFilter();
        $grid->disableTools();
        $grid->actions(function(Grid\Displayers\Actions $actions){
            $actions->disableView();
            $actions->disableEdit();
            $actions->disableDelete();

            $actions->append('<a href="#">删除</a>');
            $actions->append('<a href="/users/'.$actions->row->id.'">&nbsp;&nbsp;查看</a>');
        });
        $grid->filter(function (Grid\Filter $filter) {
            $filter->disableIdFilter();
            $filter->like('username', '姓名');
        });
        $grid->paginate($limit, 'p0');
        return $grid;
    }

    public function gridParent($id, $limit)
    {
        //家长数
        $grid = new Grid(new Classes());
        $grid->model()->from('jxt_school_classes as a')
            ->leftJoin('jxt_school_student as b', function ($join) {
                $join->on('b.class_id', 'a.class_id')->where('b.status', 1);
            })
            ->leftJoin('jxt_school_child_student as c', function ($join) {
                $join->on('c.student_id', 'b.student_id')->where('c.status', 1);
            })
            ->leftJoin('jxt_sys_child as d', function ($join) {
                $join->on('d.child_id', 'c.child_id')->where('d.status', 1);
            })
            ->rightJoin('jxt_xp_user as e', function ($join) {
                $join->on('e.id', 'd.create_by')->where('e.status', 1);
            })
            ->where('a.status', 1)
            ->where('a.school_code', $id)
            ->where('e.id', '>', 0)
            ->selectRaw('e.id,e.username,e.telephone,e.created_at,d.name_home');

        $grid->username('姓名');
        $grid->telephone('手机号');
        $grid->name_home('所绑定孩子');
        $grid->created_at('创建时间');
        $grid->disableExport();
        $grid->disableCreateButton();
        $grid->disableFilter();
        $grid->disableTools();
        $grid->actions(function(Grid\Displayers\Actions $actions){
            $actions->disableView();
            $actions->disableEdit();
            $actions->disableDelete();

            $actions->append('<a href="#">删除</a>');
            $actions->append('<a href="/users/parentDetail/'.$actions->row->id.'">&nbsp;&nbsp;查看</a>');
        });
        $grid->filter(function (Grid\Filter $filter) {
            $filter->disableIdFilter();
            $filter->like('username', '姓名');
        });
        $grid->zhPaginate($limit, 'p1');

        return $grid;
    }

    public function gridTeacherMember($id, $limit)
    {
        //老师会员数
        $grid = new Grid(new Classes());
        $grid->model()->from('jxt_school_classes as a')
            ->leftJoin('jxt_school_teacher as b', function ($join) {
                $join->on('b.class_id', 'a.class_id')->where('b.status', 1);
            })
            ->rightJoin('jxt_member_user as c', 'c.user_id', 'b.user_id')
            ->leftJoin('jxt_xp_user as d', function ($join) {
                $join->on('d.id', 'c.user_id')->where('d.status', 1);
            })
            ->leftJoin('jxt_member_price as e', 'e.id', 'c.vip_id')
            ->where('a.status', 1)
            ->where('a.school_code', $id)
            ->selectRaw('d.id,d.username,d.telephone,FROM_UNIXTIME(c.create_at) create_at,FROM_UNIXTIME(c.expiry_time) expiry_time,e.name');
        $grid->username('姓名');
        $grid->telephone('手机号');
        $grid->name('会员等级');
        $grid->create_at('创建时间');
        $grid->expiry_time('结束时间');
        $grid->disableExport();
        $grid->disableCreateButton();
        $grid->disableFilter();
        $grid->disableTools();
        $grid->actions(function(Grid\Displayers\Actions $actions){
            $actions->disableView();
            $actions->disableEdit();
            $actions->disableDelete();

            $actions->append('<a href="#">删除</a>');
            $actions->append('<a href="/users/'.$actions->row->id.'">&nbsp;&nbsp;查看</a>');
        });
        $grid->filter(function (Grid\Filter $filter) {
            $filter->disableIdFilter();
            $filter->like('username', '姓名');
        });
        $grid->paginate($limit, 'p2');

        return $grid;
    }

    public function gridParentSubscribe($id, $limit)
    {
        //家长订阅数
        $grid = new Grid(new Classes());
        $grid->model()->from('jxt_school_classes as a')
            ->leftJoin('jxt_school_student as b', function ($join) {
                $join->on('b.class_id', 'a.class_id')->where('b.status', 1);
            })
            ->leftJoin('jxt_school_child_student as c', function ($join) {
                $join->on('c.student_id', 'b.student_id')->where('c.status', 1);
            })
            ->leftJoin('jxt_sys_child as d', function ($join) {
                $join->on('d.child_id', 'c.child_id')->where('d.status', 1);
            })
            ->leftJoin('jxt_xp_user as e', function ($join) {
                $join->on('e.id', 'd.create_by')->where('e.status', 1);
            })
            ->rightJoin('jxt_subscribe_info_list as f', function ($join) {
                $join->on('f.user_id', 'e.id')->where('f.status', 1);
            })
            ->where('a.status', 1)
            ->where('a.school_code', $id)
            ->selectRaw('e.id,e.username,e.telephone,d.name_home,a.name,FROM_UNIXTIME(f.subscribe_time) subscribe_time,FROM_UNIXTIME(f.end_time) end_time');
        $grid->username('姓名');
        $grid->telephone('手机号');
        $grid->name_home('订阅孩子');
        $grid->name('订阅孩子');
        $grid->subscribe_time('订阅时间');
        $grid->end_time('结束时间');
        $grid->disableExport();
        $grid->disableCreateButton();
        $grid->disableFilter();
        $grid->disableTools();
        $grid->actions(function(Grid\Displayers\Actions $actions){
            $actions->disableView();
            $actions->disableEdit();
            $actions->disableDelete();

            $actions->append('<a href="#">删除</a>');
            $actions->append('<a href="/users/parentDetail/'.$actions->row->id.'">&nbsp;&nbsp;查看</a>');
        });
        $grid->filter(function (Grid\Filter $filter) {
            $filter->disableIdFilter();
            $filter->like('username', '姓名');
        });
        $grid->paginate($limit, 'p3');

        return $grid;
    }


}
